﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using System.Reflection;
using Excel;


namespace card
{
    public partial class FrmCount : Form
    {
        public static WebDG.Service ws = new card.WebDG.Service();
        string str_fromdate = "";
        string str_todate = "";
        string str_curdate = "";

        string str_operator = "";
        string str_ioyardid = "";
        public string cardadd
        {
            get;
            set;
        }
        public FrmCount()
        {
            InitializeComponent();
        }
        string card_add = "";
        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        /// <summary>
        /// 统计到Excel,与市入车辆登记
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click_1(object sender, EventArgs e)
        {

            //创建Application对象
            Excel.Application xApp = new Excel.ApplicationClass();

            xApp.Visible = true;

            //得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件
            Excel.Workbook xBook = xApp.Workbooks._Open(@"C:\Sample.xls",
            Missing.Value, Missing.Value, Missing.Value, Missing.Value
            , Missing.Value, Missing.Value, Missing.Value, Missing.Value
            , Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            //Excel.Workbook xBook = xApp.Workbooks.Add(Missing.Value);//新建文件的代码

            //指定要操作的Sheet，两种方式：
            Excel.Worksheet xSheet = (Excel.Worksheet)xBook.Sheets[1];

            xSheet.Cells[1, 1] = "货名";
            xSheet.Cells[1, 2] = "完成数";
            xSheet.Cells[1, 3] = "剩余数";

            int i = 0; 
            int ex_row = 2;
            StringBuilder sqlStr = new StringBuilder();

            sqlStr.AppendFormat("select cargoname as 货名,sum(case when truckstatus='1' then 1 else 0 end) as 完成数,sum(case when truckstatus='0' then 1 else 0 end) as 剩余数 from truck_login where logintime>=to_date('{0}','YYYY-MM-DD HH24:MI:SS') AND logintime<=to_date('{1}','YYYY-MM-DD HH24:MI:SS') and ioyardid='{2}' and cardadd='{3}' group by cargoname order by cargoname", str_fromdate, str_todate, str_ioyardid, cardadd);

            DataSet ds = ws.getDataSet_Serv(sqlStr.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    xSheet.Cells[ex_row, 1] = ds.Tables[0].Rows[i][0].ToString();
                    xSheet.Cells[ex_row, 2] = ds.Tables[0].Rows[i][1].ToString();
                    xSheet.Cells[ex_row, 3] = ds.Tables[0].Rows[i][2].ToString();
                    ex_row = ex_row + 1;
                }
            }
            xSheet.Cells[ex_row, 1] = "汇总";
            sqlStr = new StringBuilder();

            sqlStr.AppendFormat("select count(truckno) from truck_login where logintime>=to_date('{0}','YYYY-MM-DD HH24:MI:SS') AND logintime<=to_date('{1}','YYYY-MM-DD HH24:MI:SS') and ioyardid='{2}' and cardadd='{3}' and truckstatus='1'", str_fromdate, str_todate, str_ioyardid, cardadd);
            DataSet ds1 = ws.getDataSet_Serv(sqlStr.ToString());
            xSheet.Cells[ex_row, 2] = ds1.Tables[0].Rows[0][0].ToString();

            sqlStr = new StringBuilder();
            sqlStr.AppendFormat("select count(truckno) from truck_login where logintime>=to_date('{0}','YYYY-MM-DD HH24:MI:SS') AND logintime<=to_date('{1}','YYYY-MM-DD HH24:MI:SS') and ioyardid='{2}' and cardadd='{3}' and truckstatus='0'", str_fromdate, str_todate, str_ioyardid, cardadd);
            DataSet ds2 = ws.getDataSet_Serv(sqlStr.ToString());
            xSheet.Cells[ex_row, 3] = ds2.Tables[0].Rows[0][0].ToString();
        }
        /// <summary>
        /// 页面加载
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void FrmCount_Load(object sender, EventArgs e)
        {
            //获得发卡地点
            card_add = System.Configuration.ConfigurationManager.AppSettings["card_address"];

            str_operator = this.Text.Substring(2); 

            DateTime dt = ws.GetSysDateTime(); 

            dateTimePicker1.Value = dt;
            dateTimePicker2.Value = dt;

            comboBox1.Items.Add("市提");
            comboBox1.Items.Add("市入");

            comboBox2.Items.Add("LG1");

            comboBox1.Text = "市提";
            str_ioyardid = "0";


            comboBox1.Enabled = true;
            comboBox2.Text = str_operator;
            comboBox2.Enabled = false;
            label9.Text = "";
            label10.Text = "";
            //根据当前时间设置开始和结束时间
            if (DateTime.Now.Hour >= 6 && DateTime.Now.Hour <= 8)//早上交班时间
            {
                this.textBox_begin_time.Text = "17";
                this.textBox_end_time.Text = "7";
                DateTime yesterday = DateTime.Now.AddDays(-1);
                this.dateTimePicker1.Value = yesterday;
                //this.dateTimePicker2.Value = DateTime.Now;
            }
            if (DateTime.Now.Hour >= 16 && DateTime.Now.Hour <= 18)//晚上交班时间
            {
                this.textBox_begin_time.Text = "7";
                this.textBox_end_time.Text = "17";
            }
        } 

        /// <summary>
        /// 统计到表格
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button3_Click(object sender, EventArgs e)
        {
            if (this.textBox_begin_time.Text.ToString().Length == 0)
            {
                MessageBox.Show("请输入开始时间！");
                return;
            }
            if (this.textBox_end_time.Text.ToString().Length == 0)
            {
                MessageBox.Show("请输入结束时间！");
                return;
            }
            int begin_time_int = int.Parse(this.textBox_begin_time.Text.ToString());
            int end_time_int = int.Parse(this.textBox_end_time.Text.ToString());

            if (begin_time_int < 0 || begin_time_int > 24 || end_time_int < 0 || end_time_int > 24)
            {
                MessageBox.Show("时间必须在0和24之间！");
                return;
            }


            string sqlStr = "";

            str_fromdate = dateTimePicker1.Value.ToString("yyyy-MM-dd") + " " + this.textBox_begin_time.Text.ToString();
            str_todate = dateTimePicker2.Value.ToString("yyyy-MM-dd") + " " + this.textBox_end_time.Text.ToString();

            sqlStr = "select cargoname,sum(case when 登记数!=0 then 1 else 0 end) as 登记数,sum(case when 放车数!=0 then 1 else 0 end) as 放车数, sum(case when 剩余数!=0 then 1 else 0 end) as 剩余数,sum(case when 作废数!=0 then 1 else 0 end) as 作废数,sum(过磅费) as 过磅费,sum(退费) as 退费,coalesce(sum(过磅费),0)-coalesce(sum(退费),0) as 合计 from" +
                "(select cargoname,logintime,sum(case when truckstatus!='9' then 1 else 0 end) as 登记数,sum(0) as 放车数,sum(0) as 剩余数,sum(0) as 作废数,sum(je) as 过磅费,sum(tfje) 退费 from V_TRUCK_CARGO_LOGIN_ALL " +
                " where logintime>=to_date('" + str_fromdate + "','YYYY-MM-DD HH24:MI:SS') and logintime<=to_date('" + str_todate + "','YYYY-MM-DD HH24:MI:SS') and ioyardid='" + str_ioyardid + "' and cardadd='" + card_add + "' and ISCYCLE = '0' and truckstatus<>'9' group by cargoname,logintime " +
                "union all " +
                "select cargoname,logintime,sum(0) as 登记数,sum(case when truckstatus='2' or truckstatus='3' or truckstatus='4' or truckstatus='5' then 1 else 0 end) as 放车数,sum(0) as 剩余数,sum(0) as 作废数,0 as 过磅费,0 as 退费 from V_TRUCK_CARGO_LOGIN_ALL " +
                " where COMMANDTIME>=to_date('" + str_fromdate + "','YYYY-MM-DD HH24:MI:SS') and COMMANDTIME<=to_date('" + str_todate + "','YYYY-MM-DD HH24:MI:SS') and ioyardid='" + str_ioyardid + "' and cardadd='" + card_add + "' and ISCYCLE = '0' and truckstatus<>'9' and coalesce(CANCEL_FLAG,'0')<>'1' group by cargoname,logintime " +
                " union all " +
                "select cargoname,logintime,sum(0) as 登记数,sum(0) as 放车数,count(*) as 剩余数,sum(0) as 作废数,0 as 过磅费,0 as 退费 from V_TRUCK_CARGO_LOGIN_ALL " +
                "  where (truckstatus='0' or truckstatus='1') and ioyardid='" + str_ioyardid + "' and cardadd='" + card_add + "' and ISCYCLE = '0' and coalesce(CANCEL_FLAG,'0')<>'1' group by cargoname,logintime " +
                " union all " +
                "select cargoname,logintime,sum(0) as 登记数,sum(0) as 放车数,sum(0) as 剩余数,sum(1) as 作废数,0 as 过磅费,0 as 退费 from V_TRUCK_CARGO_LOGIN_ALL " +
                " where logintime>=to_date('" + str_fromdate + "','YYYY-MM-DD HH24:MI:SS') and logintime<=to_date('" + str_todate + "','YYYY-MM-DD HH24:MI:SS') and ioyardid='" + str_ioyardid + "' and cardadd='" + card_add + "' and ISCYCLE = '0' and truckstatus<>'9' and coalesce(CANCEL_FLAG,'0')='1' group by cargoname,logintime " +
                ") group by cargoname";
            //5-25 modify end---------- 


            DataSet ds = ws.getDataSet_Serv(sqlStr);

            dataGridView1.DataSource = ds.Tables[0];

            int i = 0;
            for (i = 0; i < 3; i++)
            {
                dataGridView1.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            }


            sqlStr = "select sum(case when 登记数!=0 then 1 else 0 end),sum(je) as 过磅费,sum(tfje) as 退费,coalesce(sum(je),0)-coalesce(sum(tfje),0) as 合计 from (select cargoname,logintime, sum(case when truckstatus!='9' then 1 else 0 end) as 登记数," +
                "sum(case when truckstatus!='9' then je else 0 end) as je,sum(case when truckstatus!='9' then tfje else 0 end) as tfje from V_TRUCK_CARGO_LOGIN_ALL " +
                " where logintime>=to_date('" + str_fromdate + "','YYYY-MM-DD HH24:MI:SS') and logintime<=to_date('" + str_todate + "','YYYY-MM-DD HH24:MI:SS')  and ioyardid='" + str_ioyardid +
                "'and cardadd='" + card_add + "' and ISCYCLE = '0' group by cargoname,logintime)";
            DataSet ds1 = ws.getDataSet_Serv(sqlStr);
            label13.Text = ds1.Tables[0].Rows[0][0].ToString();
            lblGBF.Text = ds1.Tables[0].Rows[0][1].ToString();
            lblTFJE.Text = ds1.Tables[0].Rows[0][2].ToString();
            lblHJJE.Text = ds1.Tables[0].Rows[0][3].ToString();

            sqlStr = "select sum(case when 放车数!=0 then 1 else 0 end) from (select cargoname,logintime,sum(case when truckstatus='2' or truckstatus='3' or truckstatus='4' or truckstatus='5' then 1 else 0 end) as 放车数 from V_TRUCK_CARGO_LOGIN_ALL " +
                "where COMMANDTIME>=to_date('" + str_fromdate + "','YYYY-MM-DD HH24:MI:SS') and COMMANDTIME<=to_date('" + str_todate + "','YYYY-MM-DD HH24:MI:SS')  and ioyardid='" + str_ioyardid +
                "' and cardadd='" + card_add + "' and ISCYCLE = '0' and coalesce(CANCEL_FLAG,'0')<>'1' group by cargoname,logintime)";
            DataSet ds2 = ws.getDataSet_Serv(sqlStr);
            label10.Text = ds2.Tables[0].Rows[0][0].ToString();

            sqlStr = "select sum(case when 剩余数!=0 then 1 else 0 end) from (select cargoname,logintime,count(*) as 剩余数 from V_TRUCK_CARGO_LOGIN_ALL " +
                "where (truckstatus='0' or truckstatus='1') and ioyardid='" + str_ioyardid + "' and cardadd='" + card_add + "' and ISCYCLE = '0' and coalesce(CANCEL_FLAG,'0')<>'1' group by cargoname,logintime)";
            DataSet ds3 = ws.getDataSet_Serv(sqlStr);
            label9.Text = ds3.Tables[0].Rows[0][0].ToString();

            sqlStr = "select sum(case when 登记数!=0 then 1 else 0 end) from (select cargoname,logintime, sum(case when truckstatus!='9' then 1 else 0 end) as 登记数 from V_TRUCK_CARGO_LOGIN_ALL " +
                " where logintime>=to_date('" + str_fromdate + "','YYYY-MM-DD HH24:MI:SS') and logintime<=to_date('" + str_todate + "','YYYY-MM-DD HH24:MI:SS')  and ioyardid='" + str_ioyardid +
                "'and cardadd='" + card_add + "' and ISCYCLE = '0' and coalesce(CANCEL_FLAG,'0')='1' group by cargoname,logintime)";
            DataSet ds4 = ws.getDataSet_Serv(sqlStr);
            lblZF.Text = ds4.Tables[0].Rows[0][0].ToString();

            //拉循环车辆统计
            sqlStr = "select cargoname,sum(case when 登记数!=0 then 1 else 0 end) as 登记数,sum(case when 实收费数!=0 then 1 else 0 end) as 实收费数,sum(case when 放车数!=0 then 1 else 0 end) as 放车数, sum(case when 剩余数!=0 then 1 else 0 end) as 剩余数," +
                "sum(case when 作废数!=0 then 1 else 0 end) as 作废数,sum(je) 过磅费,sum(tfje) as 退费,coalesce(sum(je),0)-coalesce(sum(tfje),0) as 合计 from" +
                "(select cargoname,logintime, sum(1) as 登记数,sum(decode(nvl(issf,'0'),'1',1,0)) as 实收费数,sum(0) as 放车数,sum(0) as 剩余数,sum(0) as 作废数,sum(je) je,sum(tfje) tfje from V_TRUCK_CARGO_LOGIN_ALL " +
                " where logintime>=to_date('" + str_fromdate + "','YYYY-MM-DD HH24:MI:SS') and logintime<=to_date('" + str_todate + "','YYYY-MM-DD HH24:MI:SS') and ioyardid='" + str_ioyardid + "' and cardadd='" + card_add + "' and truckstatus!='9' and ISCYCLE = '1' group by cargoname,logintime " +
                "union all " +
                "select cargoname,logintime,sum(0) as 登记数,0 as 实收费数,sum(case when truckstatus='2' or truckstatus='3' or truckstatus='4' or truckstatus='5' then 1 else 0 end) as 放车数,sum(0) as 剩余数,sum(0) as 作废数,0 as je,0 as tfje from V_TRUCK_CARGO_LOGIN_ALL " +
                " where COMMANDTIME>=to_date('" + str_fromdate + "','YYYY-MM-DD HH24:MI:SS') and COMMANDTIME<=to_date('" + str_todate + "','YYYY-MM-DD HH24:MI:SS') and ioyardid='" + str_ioyardid + "' and cardadd='" + card_add + "' and truckstatus!='9' and ISCYCLE = '1' and coalesce(CANCEL_FLAG,'0')<>'1' group by cargoname,logintime " +
                " union all " +
                "select cargoname,logintime,sum(0) as 登记数,0 as 实收费数,sum(0) as 放车数,count(*) as 剩余数,sum(0) as 作废数,0 as je,0 as tfje from V_TRUCK_CARGO_LOGIN_ALL " +
                "where (truckstatus='0' or truckstatus='1') and ioyardid='" + str_ioyardid + "' and cardadd='" + card_add + "' and ISCYCLE = '1' and coalesce(CANCEL_FLAG,'0')<>'1' group by cargoname,logintime " +
                " union all " +
                "select cargoname,logintime, sum(0) as 登记数,0 as 实收费数,sum(0) as 放车数,sum(0) as 剩余数,sum(1) as 作废数,0 as je,0 as tfje from V_TRUCK_CARGO_LOGIN_ALL " +
                " where logintime>=to_date('" + str_fromdate + "','YYYY-MM-DD HH24:MI:SS') and logintime<=to_date('" + str_todate + "','YYYY-MM-DD HH24:MI:SS') and ioyardid='" + str_ioyardid + "' and cardadd='" + card_add + "' and truckstatus!='9' and ISCYCLE = '1' and coalesce(CANCEL_FLAG,'0')='1' group by cargoname,logintime " +
                ") group by cargoname";
            dataGridView2.DataSource = ws.getDataSet_Serv(sqlStr).Tables[0];

            sqlStr = "select sum(case when 登记数!=0 then 1 else 0 end) as 登记数,sum(case when 实收费数!=0 then 1 else 0 end) as 实收费数,sum(je) 过磅费,sum(tfje) as 退费,coalesce(sum(je),0)-coalesce(sum(tfje),0) as 合计 from " +
                "(select cargoname,logintime, sum(1) as 登记数,sum(decode(nvl(issf,'0'),'1',1,0)) as 实收费数,sum(je) je,sum(tfje) tfje from V_TRUCK_CARGO_LOGIN_ALL " +
                " where logintime>=to_date('" + str_fromdate + "','YYYY-MM-DD HH24:MI:SS') and logintime<=to_date('" + str_todate + "','YYYY-MM-DD HH24:MI:SS')  and ioyardid='" + str_ioyardid +
                "'and cardadd='" + card_add + "' and ISCYCLE = '1' and truckstatus!='9' group by cargoname,logintime)";
            DataSet dsCycle1 = ws.getDataSet_Serv(sqlStr);
            lblCycleLogin.Text = dsCycle1.Tables[0].Rows[0]["登记数"].ToString();
            lblCycleSF.Text = dsCycle1.Tables[0].Rows[0]["实收费数"].ToString();
            lblCycleGBF.Text = dsCycle1.Tables[0].Rows[0]["过磅费"].ToString();
            lblCycleTFJE.Text = dsCycle1.Tables[0].Rows[0]["退费"].ToString();
            lblCycleHJJE.Text = dsCycle1.Tables[0].Rows[0]["合计"].ToString();

            sqlStr = "select sum(case when 放车数!=0 then 1 else 0 end) from (select cargoname,logintime,sum(case when truckstatus='2' or truckstatus='3' or truckstatus='4' or truckstatus='5' then 1 else 0 end) as 放车数 from V_TRUCK_CARGO_LOGIN_ALL " +
                "where COMMANDTIME>=to_date('" + str_fromdate + "','YYYY-MM-DD HH24:MI:SS') and COMMANDTIME<=to_date('" + str_todate + "','YYYY-MM-DD HH24:MI:SS')  and ioyardid='" + str_ioyardid +
                "' and cardadd='" + card_add + "' and ISCYCLE = '1' and coalesce(CANCEL_FLAG,'0')<>'1' group by cargoname,logintime)";
            DataSet dsCycle2 = ws.getDataSet_Serv(sqlStr);
            lblCycleFC.Text = dsCycle2.Tables[0].Rows[0][0].ToString();

            sqlStr = "select sum(case when 剩余数!=0 then 1 else 0 end) from (select cargoname,logintime,count(*) as 剩余数 from V_TRUCK_CARGO_LOGIN_ALL " +
                "where (truckstatus='0' or truckstatus='1') and ioyardid='" + str_ioyardid + "' and cardadd='" + card_add + "' and ISCYCLE = '1' and coalesce(CANCEL_FLAG,'0')<>'1' group by cargoname,logintime)";
            DataSet dsCycle3 = ws.getDataSet_Serv(sqlStr);
            lblCycleSY.Text = dsCycle3.Tables[0].Rows[0][0].ToString();

            sqlStr = "select sum(case when 登记数!=0 then 1 else 0 end) from (select cargoname,logintime, sum(1) as 登记数 from V_TRUCK_CARGO_LOGIN_ALL " +
                " where logintime>=to_date('" + str_fromdate + "','YYYY-MM-DD HH24:MI:SS') and logintime<=to_date('" + str_todate + "','YYYY-MM-DD HH24:MI:SS')  and ioyardid='" + str_ioyardid +
                "'and cardadd='" + card_add + "' and ISCYCLE = '1' and truckstatus!='9' and coalesce(CANCEL_FLAG,'0')='1' group by cargoname,logintime)";
            DataSet dsZF = ws.getDataSet_Serv(sqlStr);
            lblCycleZF.Text = dsZF.Tables[0].Rows[0][0].ToString();
        }

        private void FrmCount_FormClosed(object sender, FormClosedEventArgs e)
        {


        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBox1.Text == "市入")
            {
                str_ioyardid = "1";
            }
            else
            {
                str_ioyardid = "0";
            }
        }
    }
}
